# -*- coding: utf-8 -*-
"""
Created on Mon Sep 11 09:39:27 2023

@author: tyshixi08
"""

#调取需要的模块
import pandas as pd 
from datetime import date  
import numpy as np
import matplotlib.pyplot as plt
import math     
from sqlalchemy import create_engine
import datetime
from collections import Counter
from tqdm import tqdm
import os
#%% 数据提取class ConvBondData
#提取相关数据#
class ConvBondData(object):
    def __init__(self):
        #self.path = 'http://dataway.hhhstz.com:8888/hsic_base_fmt/cube?'
        self.path = 'http://dataway.hhhstz.com/hsic_base_fmt/cube?'
        
    # 债券估值表
    def get_convbond_valuation(self, start_date, end_date,fields=None):
        tableName = 'b_convbond_valuation'
        query_str = self.path + 'tableName=' + tableName + '&begDate=' + start_date + '&endDate=' + end_date
        if fields != None: query_str += '&fields=c_code,t_date,' + ','.join(fields)
        print(query_str)
        data = pd.read_csv(query_str)
        return data

    # 债券日行情表
    def get_convbond_market(self, start_date, end_date,fields=None):
        tableName = 'b_convbond_marketday'
        query_str = self.path + 'tableName=' + tableName + '&begDate=' + start_date + '&endDate=' + end_date
        if fields != None: query_str += '&fields=c_code,t_date,' + ','.join(fields)
        data = pd.read_csv(query_str)
        return data
    
    def get_stocka_warning(self, start_date, end_date,fields=None):
        tableName = 'b_stocka_warning'
        query_str = self.path + 'tableName=' + tableName + '&begDate=' + start_date + '&endDate=' + end_date
        if fields != None: query_str += '&fields=c_code,t_date,' + ','.join(fields)
        data = pd.read_csv(query_str)
        return data

    
#提取股票相关数据#
def get_stock_data(stock_code, str_date): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股日行情表 S_DQ_VOLUME：成交量（一手100股）,S_DQ_AMOUNTc成交额千元
    query1 = ("""select S_INFO_WINDCODE,TRADE_DT,S_DQ_CLOSE,S_DQ_HIGH,S_DQ_LOW,S_DQ_PCTCHANGE,S_DQ_VOLUME,S_DQ_TRADESTATUS,S_DQ_AMOUNT,S_DQ_ADJFACTOR
               from AShareEODPrices
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code, str_date)   
    data1 = pd.read_sql_query(query1, engine).sort_values(by=['S_INFO_WINDCODE','TRADE_DT'])
   
  # A股日行情估值指标 S_VAL_MV总市值
    query2 = ("""select S_INFO_WINDCODE,TRADE_DT,S_VAL_MV,S_VAL_PE_TTM
               from AShareEODDerivativeIndicator
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code, str_date) 
    
    data2 = pd.read_sql_query(query2, engine).sort_values(by=['S_INFO_WINDCODE','TRADE_DT'])
    # 获取正股行业代码 	SW_IND_CODE
    query3 = ("""select S_INFO_WINDCODE,SW_IND_CODE
               from AShareSWIndustriesClass
               where S_INFO_WINDCODE in %s""") % (stock_code,)
    data3 = pd.read_sql_query(query3, engine).sort_values(by=['S_INFO_WINDCODE'])
    # 获取正股ttm
    query4 = ("""select S_INFO_WINDCODE,TRADE_DT,S_DFA_NETPROFIT_TTM,S_DFA_DEDUCTEDPROFIT_TTM
               from PITFinancialFactor
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code,str_date)
    data4 = pd.read_sql_query(query4, engine).sort_values(by=['S_INFO_WINDCODE'])

    return data1, data2, data3, data4

#提取行业相关数据#
def get_stock_industry(): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股日行情表
    query1 = ("""select INDUSTRIESCODE,INDUSTRIESNAME
               from AShareIndustriesCode""")
    data1 = pd.read_sql_query(query1, engine)

    return data1

def get_stock_info(stock_code, str_date): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股利润表
    query1 = ("""select S_INFO_WINDCODE,ANN_DT,REPORT_PERIOD,OPER_REV,NET_PROFIT_EXCL_MIN_INT_INC,STATEMENT_TYPE
               from AShareIncome
               where S_INFO_WINDCODE in %s and ANN_DT > '%s'""") % (stock_code,str_date)
    data1 = pd.read_sql_query(query1, engine)
    
    # A股资产负债表
    query2 = ("""select S_INFO_WINDCODE,ANN_DT,TOT_SHRHLDR_EQY_EXCL_MIN_INT
               from AShareBalanceSheet
               where S_INFO_WINDCODE in %s and ANN_DT > '%s'""") % (stock_code,str_date)
    data2 = pd.read_sql_query(query2, engine)
    #审计意见
    query3 = ("""select S_INFO_WINDCODE,ANN_DT,S_STMNOTE_AUDIT_CATEGORY
               from AShareAuditOpinion
               where S_INFO_WINDCODE in %s and ANN_DT > '%s' """) % (stock_code, str_date)
    data3 = pd.read_sql_query(query3, engine)

    return data1, data2, data3


#%% 提取数据
def get_data(method = 1):
    #get_conv_data = ConvBondData()
    print('提取转债数据')
    path = os.getcwd()
    if method == 0:
        #df_v_s = pd.DataFrame()
        df_v_s_l = pd.DataFrame()
        df_s = pd.DataFrame()
        start_date = "2017-01-01" 
    else:
        df_s = pd.read_csv("param/Dfactor_stock_data.csv")
        #df_v_s = pd.read_csv("param/Dfactor_valuation_stock.csv")
        df_v_s_l = pd.read_csv("param/Dfactor_valuation_stock_last.csv")
        df_s.TRADE_DT = pd.to_datetime(df_s.TRADE_DT)
        #df_v_s.Date = pd.to_datetime(df_v_s.Date)
        df_v_s_l.Date = pd.to_datetime(df_v_s_l.Date) 
        start_date = str(max(df_v_s_l.Date))
        start_date = start_date[:10]
    
    end_date = str(date.today()) #结束日期为今天

    # 提取债券价格数据、估值表 df_valuation
    df_valuation = ConvBondData().get_convbond_valuation(start_date, end_date)
    
    # 提取债券日行情表 df_market
    df_market = ConvBondData().get_convbond_market(start_date, end_date)
    
    df_warning = ConvBondData().get_stocka_warning(start_date, end_date)
    df_warning = df_warning.rename(columns = {'t_tradingDate':'t_warning'})
    df_warning.t_warning = np.full([df_warning.shape[0]],1)

    
    # dataframe合并
    # 将债券数据（df_valuation）与正股指标匹配
    df_valuation = df_valuation.rename(columns = {'c_underlyingCode':'c_code'})
    
    df_valuation_stock = pd.merge(df_valuation, df_market, on = ['c_bondCode', 'index'], how = 'left')     # 合并可转债估值与可转债日行情值等信息
    df_valuation_stock = pd.merge(df_valuation_stock, df_warning, on = ['c_code', 'index'], how = 'left') 
    df_valuation_stock = df_valuation_stock.rename(columns = {'index_x':'index'})
    df_valuation_stock['index'] = pd.to_datetime(df_valuation_stock['index'] )
    
    
    print('提取正股数据')
    # 提取正股数据，包括成交额、行业
    stockCode = df_valuation_stock['c_code'].unique()
    data = get_stock_data(tuple(stockCode), start_date[:4] + start_date[5:7] + start_date[8:])
    
    df_stock =  pd.merge(data[0], data[1], on = ['S_INFO_WINDCODE', 'TRADE_DT'], how = 'left')
    df_stock =  pd.merge(df_stock, data[2], on = ['S_INFO_WINDCODE'], how = 'left')
    df_stock =  pd.merge(df_stock, data[3], on = ['S_INFO_WINDCODE','TRADE_DT'], how = 'left')
    df_stock.TRADE_DT = pd.to_datetime(df_stock.TRADE_DT)
    df_stock_data = data[0]
    df_stock_data.TRADE_DT = pd.to_datetime(df_stock_data.TRADE_DT)
    df_stock = df_stock.rename(columns = {'S_INFO_WINDCODE':'c_code', 'TRADE_DT' : 'index'})    
    df_valuation_stock = pd.merge(df_valuation_stock, df_stock, on = ['c_code', 'index'], how = 'left')
    
    # 将日期列名改为Date
    df_valuation_stock = df_valuation_stock.rename(columns = {'index':'Date'})
    df_valuation_stock = df_valuation_stock.drop_duplicates(subset=("Date", 'c_bondCode'))
    df_valuation_stock = df_valuation_stock.sort_values(by = "Date")
    
       
    # 获取月份最后一天的日期的代码
    last_day = df_valuation_stock["Date"].unique()
    df_valuation_stock_last = df_valuation_stock[df_valuation_stock['Date'].isin(last_day)]
    dt_ind = get_stock_industry()
    dt_ind = dt_ind.rename(columns={'INDUSTRIESCODE': 'SW_IND_CODE'})
    df_valuation_stock_last.SW_IND_CODE = df_valuation_stock_last.SW_IND_CODE.str[:4]+ 12*'0'
    df_valuation_stock_last = pd.merge(df_valuation_stock_last, dt_ind, on = ['SW_IND_CODE'], how = 'left')
    #industries = list(set(df_valuation_stock_last.INDUSTRIESNAME))[1:]
    date_n = len(last_day)
    

    # 提取股票信息
    data2 = get_stock_info(tuple(stockCode), "2017-01-01")
    data0 = data2[0][(data2[0]["STATEMENT_TYPE"] == "408001000") & (data2[0].REPORT_PERIOD.apply(lambda x: x[4:] == "1231"))]
    data0 = data0[data0.REPORT_PERIOD != np.nan]
    df_stock_info =  pd.merge(data0, data2[1], on = ['S_INFO_WINDCODE', 'ANN_DT'], how = 'left')
    df_stock_info =  pd.merge(df_stock_info, data2[2], on = ['S_INFO_WINDCODE', 'ANN_DT'], how = 'right')
    df_stock_info = df_stock_info.drop_duplicates(subset=("ANN_DT", 'S_INFO_WINDCODE'))
    df_stock_info.ANN_DT = pd.to_datetime(df_stock_info.ANN_DT)
    result=pd.DataFrame()
    
    
    print("源数据输出")
    # info2是需要输出的数据
    for j in tqdm(range(stockCode.shape[0])):
        temp_info = df_stock_info[df_stock_info.S_INFO_WINDCODE == stockCode[j]]
        temp_date = np.sort(temp_info.ANN_DT)
        temp_n = temp_info.shape[0]
        temp_res = df_valuation_stock[df_valuation_stock.c_code == stockCode[j]][["Date", "c_code"]]
        temp_res[df_stock_info.columns] = np.ones(shape=[temp_res.shape[0], len(df_stock_info.columns)])
    
        for k in range(1,temp_n):
            temp_len = np.sum((temp_res.Date < temp_date[k]) & (temp_res.Date >= temp_date[k - 1]))
            if temp_len ==0:
                continue
            temp = np.repeat(np.array(temp_info[temp_info.ANN_DT == temp_date[k - 1]]),temp_len, axis = 0)
            temp_res.loc[(temp_res.Date < temp_date[k]) & (temp_res.Date >= temp_date[k - 1]),df_stock_info.columns] = list(temp)
            
        temp_len = np.sum(temp_res.Date >= temp_date[temp_n-1])
        if temp_len ==0:
            result = pd.concat([result, temp_res])
            continue
        temp = np.repeat(np.array(temp_info[temp_info.ANN_DT == temp_date[temp_n-1]]),temp_len, axis = 0)
        temp_res.loc[temp_res.Date >= temp_date[temp_n - 1],df_stock_info.columns] = list(temp)  
        result = pd.concat([result, temp_res])
        
    result = result[result.iloc[:,-1] !=1]
    df_valuation_stock_last = pd.merge(df_valuation_stock_last, result, on = ['Date','c_code'], how = 'left')
    df_valuation_stock_last = df_valuation_stock_last.drop_duplicates(subset=("Date", 'c_bondCode'))
    
    df_stock_data = pd.concat([df_s, df_stock_data])
    df_valuation_stock_last = pd.concat([df_v_s_l, df_valuation_stock_last])
    df_stock_data = df_stock_data.drop_duplicates(subset=("S_INFO_WINDCODE", 'TRADE_DT'))
    df_valuation_stock_last = df_valuation_stock_last.drop_duplicates(subset=("Date", 'c_bondCode'))
    df_stock_data.to_csv(r"param/Dfactor_stock_data.csv", index = False)
    df_valuation_stock_last.to_csv(r"param/Dfactor_valuation_stock_last.csv", index = False)

    
    # 提取日期数据
    date_all = df_valuation_stock["Date"].unique()
    date_all = np.sort(date_all)
    return df_valuation_stock_last,df_stock_data, date_n,last_day,date_all